package pl.register.android.database.crud;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import pl.register.android.course.Course;
import pl.register.android.database.dao.StudentGroupDao;
import pl.register.android.database.dao.StudentsDao;
import pl.register.android.database.tables.*;
import pl.register.android.group.StudentsGroup;
import pl.register.android.student.Student;
import pl.register.android.year.Year;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * @author Mateusz Strzelecki
 * @since 2013-04-26
 */
public class StudentsCRUD implements StudentsDao {
    private static String TAG = StudentsCRUD.class.getSimpleName();

    private SQLiteDatabase database;

    private StudentInYearCRUD studentInYearCRUD;

    private StudentClassNumberCRUD studentClassNumberCRUD;

    public StudentsCRUD(SQLiteDatabase database) {
        this.database = database;
        studentClassNumberCRUD = new StudentClassNumberCRUD(database);
        studentInYearCRUD = new StudentInYearCRUD(database);
    }

    //  GETTERS
    @Override
    public List<Student> findStudentsBy(Year year, Course course) {
        List<Student> students = new ArrayList<Student>();
        String query = "SELECT " +
                StudentDBTable.STUDENT_ID + ", " +
                StudentDBTable.STUDENT_FIRST_NAME + ", " +
                StudentDBTable.STUDENT_LAST_NAME + ", " +
                StudentDBTable.STUDENT_PHONE_NUMBER + ", " +
                StudentDBTable.STUDENT_EMAIL + ", " +
                StudentDBTable.STUDENT_CITY + ", " +
                StudentClassNumberDBTable.CLASSNUMBER_CLASS_NUMBER +
                " FROM " + StudentDBTable.TABLE_NAME +
                " INNER JOIN " + StudentClassNumberDBTable.TABLE_NAME +
                " ON " + StudentDBTable.STUDENT_ID + " = " + StudentClassNumberDBTable.CLASSNUMBER_FK_STUDENT_ID +
                " INNER JOIN " + StudentInYearDBTable.TABLE_NAME +
                " ON " + StudentDBTable.STUDENT_ID + " = " + StudentInYearDBTable.STUDENTINYEAR_FK_STUDENT_ID +
                " WHERE " + StudentClassNumberDBTable.CLASSNUMBER_FK_COURSE_ID + " = " + course.getId() +
                " AND " + StudentInYearDBTable.STUDENTINYEAR_FK_YEAR_ID + " = " + year.getId() + ";";
        Log.d(TAG, " get student for year query: " + query);
        Cursor cursor = database.rawQuery(query, null);
        while (cursor.moveToNext()) {
            Long studentId = cursor.getLong(cursor.getColumnIndex(StudentDBTable.ID));
            String studentFirstName = cursor.getString(cursor.getColumnIndex(StudentDBTable.FIRST_NAME));
            String studentLastName = cursor.getString(cursor.getColumnIndex(StudentDBTable.LAST_NAME));
            String studentPhoneNo = cursor.getString(cursor.getColumnIndex(StudentDBTable.PHONE_NUMBER));
            String studentEmail = cursor.getString(cursor.getColumnIndex(StudentDBTable.EMAIL));
            String studentCity = cursor.getString(cursor.getColumnIndex(StudentDBTable.CITY));
            String studentClassNo = cursor.getString(cursor.getColumnIndex(StudentClassNumberDBTable.CLASS_NUMBER));
            students.add(new Student(studentId, studentFirstName, studentLastName, studentPhoneNo, studentEmail, studentCity, studentClassNo));
        }
        cursor.close();
        Collections.sort(students);
        return students;
    }

    @Override
    public Student findStudentsBy(Long studentId, Course course) {
        Student student = null;
        String query = "SELECT " +
                StudentDBTable.STUDENT_FIRST_NAME + ", " +
                StudentDBTable.STUDENT_LAST_NAME + ", " +
                StudentDBTable.STUDENT_PHONE_NUMBER + ", " +
                StudentDBTable.STUDENT_EMAIL + ", " +
                StudentDBTable.STUDENT_CITY + ", " +
                StudentClassNumberDBTable.CLASSNUMBER_CLASS_NUMBER +
                " FROM " + StudentDBTable.TABLE_NAME +
                " INNER JOIN " + StudentClassNumberDBTable.TABLE_NAME +
                " ON " + StudentDBTable.STUDENT_ID + " = " + StudentClassNumberDBTable.CLASSNUMBER_FK_STUDENT_ID +
                " WHERE " + StudentClassNumberDBTable.CLASSNUMBER_FK_COURSE_ID + " = " + course.getId() +
                " AND " + StudentDBTable.STUDENT_ID + " = " + studentId + ";";
        Log.d(TAG, " get student for year query: " + query);
        Cursor cursor = database.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            String studentFirstName = cursor.getString(cursor.getColumnIndex(StudentDBTable.FIRST_NAME));
            String studentLastName = cursor.getString(cursor.getColumnIndex(StudentDBTable.LAST_NAME));
            String studentPhoneNo = cursor.getString(cursor.getColumnIndex(StudentDBTable.PHONE_NUMBER));
            String studentEmail = cursor.getString(cursor.getColumnIndex(StudentDBTable.EMAIL));
            String studentCity = cursor.getString(cursor.getColumnIndex(StudentDBTable.CITY));
            String studentClassNo = cursor.getString(cursor.getColumnIndex(StudentClassNumberDBTable.CLASS_NUMBER));
            student = new Student(studentId, studentFirstName, studentLastName, studentPhoneNo, studentEmail, studentCity, studentClassNo);
        }
        cursor.close();
        return student;
    }

    @Override
    public List<Student> findStudentsBy(StudentsGroup studentsGroup, Course course) {
        List<Student> students = new ArrayList<Student>();
        String query = "SELECT " +
                StudentDBTable.STUDENT_ID + ", " +
                StudentDBTable.STUDENT_FIRST_NAME + ", " +
                StudentDBTable.STUDENT_LAST_NAME + ", " +
                StudentDBTable.STUDENT_PHONE_NUMBER + ", " +
                StudentDBTable.STUDENT_EMAIL + ", " +
                StudentDBTable.STUDENT_CITY + ", " +
                StudentClassNumberDBTable.CLASSNUMBER_CLASS_NUMBER +
                " FROM " + StudentDBTable.TABLE_NAME +
                " INNER JOIN " + StudentClassNumberDBTable.TABLE_NAME +
                " ON " + StudentDBTable.STUDENT_ID + " = " + StudentClassNumberDBTable.CLASSNUMBER_FK_STUDENT_ID +
                " INNER JOIN " + StudentInGroupsDBTable.TABLE_NAME +
                " ON " + StudentDBTable.STUDENT_ID + " = " + StudentInGroupsDBTable.STUDENTINGROUP_FK_STUDENT_ID +
                " WHERE " + StudentInGroupsDBTable.STUDENTINGROUP_FK_GROUP_ID + " = " + studentsGroup.getId() +
                " AND " + StudentClassNumberDBTable.CLASSNUMBER_FK_COURSE_ID + " = " + course.getId() + ";";
        Log.d(TAG, " get student for student group query: " + query);
        Cursor cursor = database.rawQuery(query, null);
        while (cursor.moveToNext()) {
            Long studentId = cursor.getLong(cursor.getColumnIndex(StudentDBTable.ID));
            String studentFirstName = cursor.getString(cursor.getColumnIndex(StudentDBTable.FIRST_NAME));
            String studentLastName = cursor.getString(cursor.getColumnIndex(StudentDBTable.LAST_NAME));
            String studentPhoneNo = cursor.getString(cursor.getColumnIndex(StudentDBTable.PHONE_NUMBER));
            String studentEmail = cursor.getString(cursor.getColumnIndex(StudentDBTable.EMAIL));
            String studentCity = cursor.getString(cursor.getColumnIndex(StudentDBTable.CITY));
            String studentClassNo = cursor.getString(cursor.getColumnIndex(StudentClassNumberDBTable.CLASS_NUMBER));
            students.add(new Student(studentId, studentFirstName, studentLastName, studentPhoneNo, studentEmail, studentCity, studentClassNo));
        }
        cursor.close();
        Collections.sort(students);
        return students;
    }

    @Override
    public List<Student> findStudentsWithoutGroupBy(Year year, Course course) {
        List<Student> students = new ArrayList<Student>();
        String query = "SELECT " +
                StudentDBTable.STUDENT_ID + ", " +
                StudentDBTable.STUDENT_FIRST_NAME + ", " +
                StudentDBTable.STUDENT_LAST_NAME + ", " +
                StudentDBTable.STUDENT_PHONE_NUMBER + ", " +
                StudentDBTable.STUDENT_EMAIL + ", " +
                StudentDBTable.STUDENT_CITY + ", " +
                StudentClassNumberDBTable.CLASSNUMBER_CLASS_NUMBER +
                " FROM " + StudentDBTable.TABLE_NAME +
                " INNER JOIN " + StudentClassNumberDBTable.TABLE_NAME +
                " ON " + StudentDBTable.STUDENT_ID + " = " + StudentClassNumberDBTable.CLASSNUMBER_FK_STUDENT_ID +
                " INNER JOIN " + StudentInYearDBTable.TABLE_NAME +
                " ON " + StudentDBTable.STUDENT_ID + " = " + StudentInYearDBTable.STUDENTINYEAR_FK_STUDENT_ID +
                " LEFT JOIN " + StudentInGroupsDBTable.TABLE_NAME +
                " ON " + StudentDBTable.STUDENT_ID + " = " + StudentInGroupsDBTable.STUDENTINGROUP_FK_STUDENT_ID +
                " WHERE " + StudentInYearDBTable.STUDENTINYEAR_FK_YEAR_ID + " = " + year.getId() +
                " AND " + StudentInGroupsDBTable.STUDENTINGROUP_FK_GROUP_ID + " IS NULL;";
        Log.d(TAG, " get student without group query: " + query);
        Cursor cursor = database.rawQuery(query, null);
        while (cursor.moveToNext()) {
            Long studentId = cursor.getLong(cursor.getColumnIndex(StudentDBTable.ID));
            String studentFirstName = cursor.getString(cursor.getColumnIndex(StudentDBTable.FIRST_NAME));
            String studentLastName = cursor.getString(cursor.getColumnIndex(StudentDBTable.LAST_NAME));
            String studentPhoneNo = cursor.getString(cursor.getColumnIndex(StudentDBTable.PHONE_NUMBER));
            String studentEmail = cursor.getString(cursor.getColumnIndex(StudentDBTable.EMAIL));
            String studentCity = cursor.getString(cursor.getColumnIndex(StudentDBTable.CITY));
            String studentClassNo = cursor.getString(cursor.getColumnIndex(StudentClassNumberDBTable.CLASS_NUMBER));
            students.add(new Student(studentId, studentFirstName, studentLastName, studentPhoneNo, studentEmail, studentCity, studentClassNo));
        }
        cursor.close();
        Collections.sort(students);
        return students;
    }

    //  SETTER / UPDATER
    @Override
    public Student save(Student student, Year year, Course course) {
        if (student.isPersist()) {
            student = update(student, course);
        } else {
            student = insert(student, year, course);
        }
        return student;
    }

    private Student insert(Student student, Year year, Course course) {
        ContentValues insertValues = new ContentValues();
        insertValues.put(StudentDBTable.FIRST_NAME, student.getFirstName());
        insertValues.put(StudentDBTable.LAST_NAME, student.getLastName());
        insertValues.put(StudentDBTable.PHONE_NUMBER, student.getPhoneNo());
        insertValues.put(StudentDBTable.EMAIL, student.getEmailAddress());
        insertValues.put(StudentDBTable.CITY, student.getCity());
        long rowId = database.insert(StudentDBTable.TABLE_NAME, null, insertValues);
        student.setId(rowId);
        studentClassNumberCRUD.insert(student, course);
        studentInYearCRUD.insert(student, year);

        return student;
    }

    public Student update(Student student, Course course) {
        String where = StudentDBTable.ID + " = " + student.getId();
        ContentValues updateValues = new ContentValues();
        updateValues.put(StudentDBTable.FIRST_NAME, student.getFirstName());
        updateValues.put(StudentDBTable.LAST_NAME, student.getLastName());
        updateValues.put(StudentDBTable.PHONE_NUMBER, student.getPhoneNo());
        updateValues.put(StudentDBTable.EMAIL, student.getEmailAddress());
        updateValues.put(StudentDBTable.CITY, student.getCity());
        long res = database.update(StudentDBTable.TABLE_NAME, updateValues, where, null);
        studentClassNumberCRUD.update(student, course);
        return student;
    }

    //  REMOVE
    @Override
    public void deleteFrom(Student student, Year year) {
        studentInYearCRUD.delete(student, year);
    }

}
